﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace Business
{
   public class Comment
    {
        CreateConn obj;
        DataTable dt;
       int reco1=0,reco2=0,reco3=0;
      public  static int [] recos = new int[3];
       public Comment()
      {
       
            obj = new CreateConn();
        }
        public DataTable getTemplate()
        {
            try
            {
                string query = "SELECT * FROM StandardComments";
                dt =  obj.SQLConnDataTable(query);
            }catch(Exception e){
                String Errmsg = e.ToString();

            }
            return dt;
        }
        public DataTable getFaults(int componentID)
        {
            string query = "SELECT * FROM FaultsCondition WHERE faultType=1 and fault_id IN (SELECT fault_id" +
                " FROM Category_Fault WHERE category_id IN(" +
                "SELECT category_id FROM Components WHERE " +
                "component_id='" + componentID + "') OR " +
                "category_id=1)";
            try
            {
                dt = obj.SQLConnDataTable(query);
            }catch(Exception e){
                String Errmsg = e.ToString();
            }
            return dt;
        }
        public void copyOldReport(int componentID, string dateOfSurvey)
        {
            DataTable historyDT = getPreviousRecomm(componentID);
            foreach (DataRow dr in historyDT.Rows)
            {
                int RC1 = 0;
                int RC2 = 0, RC3 = 0;
                if (!dr.IsNull("recommendID_1"))
                {
                    RC1 = Int32.Parse(dr["recommendID_1"].ToString());
                }
                if (dr.IsNull("recommendID_2"))
                {
                }
                else if (dr.IsNull("recommendID_3"))
                {
                    RC2 = Int32.Parse(dr["recommendID_2"].ToString());
                }
                else
                {
                    RC2 = Int32.Parse(dr["recommendID_2"].ToString());
                    RC3 = Int32.Parse(dr["recommendID_3"].ToString());

                }
                int severityID = Int32.Parse(dr["status_id"].ToString());
                string comments = dr["comments"].ToString();                
                if (!dr.IsNull("resurvey_id"))
                {
                    int resurvyID = Int32.Parse(dr["resurvey_id"].ToString());
                   insertNewreport(componentID, severityID, comments, dateOfSurvey, RC1, RC2, RC3, resurvyID);
                }
                else
                {

                    insertNewreport(componentID, severityID, comments, dateOfSurvey, RC1, RC2, RC3, 0);

                }
            }
        }
        public bool insertNewreport(int componentID,int status_id,string comments,string dateRecorded,int reco1,int reco2,int reco3,int resury_id )
        {
            string query = "";
            string daterecieved = dateRecorded;
            System.Globalization.DateTimeFormatInfo dateInfo = new System.Globalization.DateTimeFormatInfo();
            dateInfo.ShortDatePattern = "dd/MM/yyyy";
           // dateInfo.ShortDatePattern = "yyyy/MM/dd";
            DateTime dt2 = Convert.ToDateTime(dateRecorded,dateInfo);
           // DateTime date2 = date.Date;
         // DateTime date = new DateTime();
          //  DateTime date = new DateTime();
           // DateTime date1 = new DateTime(2008, 6, 1, 7, 47, 0);
           // DateTime dt2 = date1.Date;
            string datestring = "2008/06/01";
            //string select_query = "SELECT component_id FROM ComponentStatus"
            //    + "WHERE component_id='" + componentID + "' and dateRecorded='" + dt2.Date+"'";
            //CreateConn obj_conn = new CreateConn();
              string delete_query = "DELETE FROM ComponentStatus"
                + " WHERE component_id='" + componentID + "' and dateRecorded=convert(datetime,'" + dt2.Date+"',103)";
         CreateConn obj_conn = new CreateConn();
            try
            {
                obj_conn.SQLConnDataTable(delete_query);
            }catch(Exception e){
                string errmsg = e.ToString();
            }
           //DateTime date = Convert.ToDateTime(dateRecorded);
            if (resury_id == 0)
            {
                if (reco2 == 0)
                {
                    query = "INSERT INTO ComponentStatus(component_id,status_id,comments,dateRecorded,recommendID_1) values('" + componentID
                      + "','" + status_id + "','" + comments + "','" + dt2.Date + "','" + reco1
                      + "')";
                }
                else if (reco3 == 0)
                {
                    query = "INSERT INTO ComponentStatus(component_id,status_id,comments,dateRecorded,recommendID_1,recommendID_2) values('" + componentID
                    + "','" + status_id + "','" + comments + "','" + dt2.Date + "','" + reco1 + "','" + reco2+"')";
                }
                else
                {
                    query = "INSERT INTO ComponentStatus(component_id,status_id,comments,dateRecorded,recommendID_1,recommendID_2,recommendID_3) values('" + componentID
                    + "','" + status_id + "','" + comments + "','" + dt2.Date + "','" + reco1 + "','" + reco2 + "','" + reco3+"')";
                }
                //query = "INSERT INTO ComponentStatus(component_id,status_id,comments,dateRecorded) values('" + componentID
                //   + "','" + status_id + "','" + comments + "','" + dt2.Date + "')";
               
            }
            else
            {
                if (reco2 == 0)
                {
                    query = "INSERT INTO ComponentStatus(component_id,status_id,comments,dateRecorded,recommendID_1,resurvey_id) values('" + componentID
                      + "','" + status_id + "','" + comments + "','" + dt2.Date + "','" + reco1
                      + "','" + resury_id + "')";
                }
                else if (reco3 == 0)
                {
                    query = "INSERT INTO ComponentStatus(component_id,status_id,comments,dateRecorded,recommendID_1,recommendID_2,Resurvey_id) values('" + componentID
                    + "','" + status_id + "','" + comments + "','" + dt2.Date + "','" + reco1 + "','" + reco2
                    + "','" + resury_id + "')";
                }
                else
                {
                    query = "INSERT INTO ComponentStatus(component_id,status_id,comments,dateRecorded,recommendID_1,recommendID_2,recommendID_3,Resurvey_id) values('" + componentID
                    + "','" + status_id + "','" + comments + "','" + dt2.Date + "','" + reco1 + "','" + reco2 + "','" + reco3
                    + "','" + resury_id + "')";
                }
            }
           try
           {
               dt = obj.SQLConnDataTable(query);

           }catch(Exception e){
               string errmsg = e.ToString();
           }
            return true;
        }
   
       public DataTable getPreviousRecomm(int component_id)
        {
            
            string query = "select status_id,comments,dateRecorded,recommendID_1,recommendID_2,recommendID_3,resurvey_id from ComponentStatus WHERE "+
                "dateRecorded IN (SELECT TOP 1 dateRecorded FROM ComponentStatus WHERE component_id='"+component_id+"' ORDER BY dateRecorded DESC) and component_id='"+component_id+"'";
            try
            {
                dt = obj.SQLConnDataTable(query);
            }
            catch(Exception e){
                String Errmsg = e.ToString();
            }
              return dt;
        }
       
       
  
        public DataTable getRecomm2()
        {
            string query = "SELECT * FROM Recommendation";
            try
            {

                dt = obj.SQLConnDataTable(query);
            }
            catch (Exception e)
            {
                String Errmsg = e.ToString();
            }
            return dt;
        }
        public DataTable getRecomm(int Fault1ID, int statusID, int Fault2ID, int status2ID, int Fault3ID, int status3ID)//Get Recommendation using given fault and severity
        {

            DataTable dt2 = null;
            DataTable result_table = new DataTable();

            string query = "SELECT * FROM FaultRecommendation WHERE Fault_id = '" + Fault1ID + "'AND Status_id ='" + statusID + "'";
            try
            {
            
                dt = obj.SQLConnDataTable(query);
                result_table = dt.Copy();
                result_table.Rows.Clear();
            }catch(Exception e){
                String Errmsg = e.ToString();
              }
           if (Fault2ID != 0&&Fault3ID==0)
            {
               string query2 = "SELECT * FROM FaultRecommendation WHERE Fault_id = '" + Fault2ID + "'AND Status_id ='" + status2ID + "'";
               dt2 = obj.SQLConnDataTable(query2);
               result_table = obj.SQLConnDataTable(query2);
               result_table.Rows.Clear();
               if (dt2.Rows.Count>0)
               {
               
                   foreach (DataRow dr in dt.Rows)
                   {
                       string comment1 = dr["recommendation"].ToString();
                       foreach (DataRow dr2 in dt2.Rows)
                       {
                           string comment2 = dr2["recommendation"].ToString();
                          
                           if (comment1.Equals(comment2))
                           {
                               DataRow dr_res = result_table.NewRow();
                               dr_res["recommend_id"] = Int32.Parse(dr2["recommend_id"].ToString());
                               dr_res["recommendation"] = comment2;
                               result_table.Rows.Add(dr_res);
                               reco1 = Int32.Parse(dr["recommend_id"].ToString());
                               reco2 = Int32.Parse(dr2["recommend_id"].ToString());

                               string query4 = "SELECT * FROM FaultRecommendation WHERE recommend_id='" + reco1 + "'";
                               dt = obj.SQLConnDataTable(query4);
                           }
                          

                       }

                   }
                  
               }
               return result_table;
           
            }
           else if (Fault2ID != 0 && Fault3ID != 0)
           {
               string query2 = "SELECT * FROM FaultRecommendation WHERE Fault_id = '" + Fault2ID + "'AND Status_id ='" + status2ID + "'";
               dt2 = obj.SQLConnDataTable(query2);
               string query3 = "SELECT * FROM FaultRecommendation WHERE Fault_id = '" + Fault3ID + "'AND Status_id ='" + status3ID + "'";
               DataTable dt3 = obj.SQLConnDataTable(query3);
               foreach (DataRow dr in dt.Rows)
               {
                   string comment1 = dr["recommendation"].ToString();
                   if (dt2.Rows.Count > 0 && dt3.Rows.Count > 0)
                   {
                       foreach (DataRow dr2 in dt2.Rows)
                       {
                           string comment2 = dr2["recommendation"].ToString();
                           foreach (DataRow dr3 in dt3.Rows)
                           {
                               string comment3 = dr3["recommendation"].ToString();
                               if (comment1.Equals(comment2) && comment1.Equals(comment3))
                               {
                                   DataRow dr_res = result_table.NewRow();
                                   dr_res["recommend_id"] = Int32.Parse(dr2["recommend_id"].ToString());
                                   dr_res["recommendation"] = comment2;
                                   result_table.Rows.Add(dr_res);
                                   reco1 = Int32.Parse(dr["recommend_id"].ToString());
                                   reco2 = Int32.Parse(dr2["recommend_id"].ToString());
                                   reco3 = Int32.Parse(dr3["recommend_id"].ToString());
                                
                     
                               }
                        
                           }
                       }

                   }
               

               }
               recos[0] = reco1;
               recos[1] = reco2;
               recos[2] = reco3;
               return result_table;
           }
           else
           {
               return dt;
           }
        }
        public void insertTemplate(string template,int status_id)
        {
            try
            {
                string query = "INSERT INTO StandardComments (commentDescription,status_id) VALUES('" + template + "','" + status_id + "')";
                dt = obj.SQLConnDataTable(query);
            }
            catch (Exception e)
            {
                String Errmsg = e.ToString();
            }

        }
        public DataTable getResurvey()
        {
            try
            {
                string query = "SELECT * FROM Resurvey";
                dt = obj.SQLConnDataTable(query);
            }catch(Exception e){
            }
            return dt;
        }
       public void InsertMatrix(string daterecorded,int machine_id,int matrix_no){
           try
           {
               string query = "INSERT INTO Table MachineMatrix(date_recorded,machine_id,matrix_no) values" +
                   "(convert(datetime,'" + daterecorded + "', 103),'" + machine_id + "','" + matrix_no + "')";
               dt = obj.SQLConnDataTable(query);
           }
           catch (Exception e)
           {
               String Errmsg = e.ToString();
           }

   }
    }
}
